﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace RestaurantWebService
{
    public class StatManager
    {
		private const string mostPopularMealSql = "SELECT mealNumber FROM (SELECT mealNumber, max(rows) FROM (SELECT mealNumber, count(*) AS rows FROM meal_order GROUP BY mealNumber) A) B";
		private const string mostPopularTableSql = "SELECT tableNumber FROM (SELECT tableNumber, max(rows) FROM (SELECT tableNumber, count(*) AS rows FROM meal_order GROUP BY tableNumber) A) B";
		private const string noOfMealsSoldSql = "SELECT count(*) FROM meal_order WHERE mealNumber = {0}";
        private const string noOfAllMealsSoldSql = "SELECT count(*) FROM meal_order";

        private DatabaseManager dbManager;

        public StatManager()
        {
            dbManager = new DatabaseManager();
        }

        public Meal getMostPopularMeal()
        {
			int mealNumber = (int) dbManager.executeScalar(mostPopularMealSql);
			return Menu.Instance.getMealByNumber(mealNumber);
        }

        public Table getMostPopularTable()
        {
			int tableNumber = (int) dbManager.executeScalar(mostPopularTableSql);
            Table table = new Table();
			table.TableNumber = tableNumber;
			return table;
        }

        public int getNoOfAllMealSold()
        {
            return (int)dbManager.executeScalar(noOfAllMealsSoldSql);
        }

        public int getNoOfMealSold(Meal meal)
        {
            return (int) dbManager.executeScalar(String.Format(noOfMealsSoldSql, meal.MealNumber));
        }
    }
}